/*
 * Copyright (c) 2012 Aleksander Zotov, Benedikt Markt, Aliaksei Babkovich
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"),
 *  to deal in the Software without restriction, including without limitation
 *  the rights to use, copy, modify, merge, publish, distribute, sublicense,
 *  and/or sell copies of the Software, and to permit persons to whom the
 *  Software is furnished to do so, subject to the following conditions:
 *  
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 *  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 *  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 *  IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
 *  DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR
 *  OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
 *  THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;

import simulation.Simulation;
import user.User;
import user.UserManager;
import utils.ApplicationPreferences;
import utils.Debug;

/**
 * This class holds all methods to store and delete simulations and user from
 * corresponding database tables
 * 
 * @author Aliaksei Babkovich, Benedikt Markt
 * @see UserManager
 * @see Simulations
 * @see SimulationManager
 */
public class DatabaseManager {

	private final static String USER_TABLE_NAME = "SPUsers";
	private final static String SIM_TABLE_NAME = "Simulations";

	private final static int SIM_NAME_COL = 1;
	private final static int SIM_DESC_COL = 2;
	private final static int SIM_COLDIST_COL = 3;
	private final static int SIM_STARTTIME_COL = 4;
	private final static int SIM_ENDTIME_COL = 5;
	private final static int SIM_REFDATA_COL = 6;
	private final static int SIM_USERNAME_COL = 7;
	private final static int SIM_REFSYS_COL = 8;
	private final static int SIM_COLCOUNT_COL = 9;
	private final static int SIM_ID_COL = 10;
	private final static int SIM_TIMESTAMP_COL = 11;
	private final static int SIM_RESULTS_COL = 12;

	private final static int USER_NAME_COL = 1;
	private final static int USER_PASSWORD_COL = 2;
	private final static int USER_ISADMIN_COL = 3;
	private final static int USER_LANGUAGE_COL = 4;
	private final static int USER_REFSYS_COL = 5;

	String url;
	String dbName;
	String driver;
	String username;
	String password;

	public DatabaseManager() {
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			Debug.log(Debug.FATAL, "Can't load H2 driver!");
		}
		ApplicationPreferences prefs = ApplicationPreferences.getInstance();
		url = "jdbc:h2:tcp://" + prefs.getDbURL() + "/~/";
		//url = "jdbc:h2:~/" + prefs.getDbURL();
		dbName = prefs.getDbName();
		username = prefs.getDbUsername();
		password = prefs.getDbPass();
		
		DriverManager.setLoginTimeout(3);
		createUserTable();
		createSimTable();
		/*if (!tableExists(USER_TABLE_NAME)) {
			createUserTable();
		}

		if (!tableExists(SIM_TABLE_NAME)) {
			createSimTable();
		}*/
	}

	/**
	 * Create table in DB for Simulations
	 * 
	 */
	private void createSimTable() {
		if (isAvailable()) {
			Connection createSimTableConnection = null;
			try {
				createSimTableConnection = getConnection();
				Statement createSimTableStatement = createSimTableConnection
						.createStatement();

				/*
				 * Query for creating MySQL table Simulations with columns :
				 * name, description, collisionDistance, referenceSystem,
				 * startTime, endTime, identifier, timeStamp. Primary key -
				 * identifier.
				 */
				String SIMULATION_TABLE = "CREATE TABLE IF NOT EXISTS "
						+ SIM_TABLE_NAME
						+ " ("
						+ " name VARCHAR (20), description VARCHAR (300),"
						+ " collisionDistance FLOAT, startTime TIMESTAMP, endTime TIMESTAMP, refData VARCHAR(1000),"
						+ " username VARCHAR (12), referenceSystem VARCHAR (3), collisionCount INT,"
						+ " identifier VARCHAR (40) PRIMARY KEY, timeStamp TIMESTAMP, results VARCHAR(1000))";
				createSimTableStatement.executeUpdate(SIMULATION_TABLE);
				createSimTableConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				Debug.log(Debug.ERROR, "Failed to connect!");
				e.printStackTrace();
			}
		}
	}

	/**
	 * Saving Simulation in DB TODO Refactor and clean up
	 * 
	 * @param simulation
	 */
	public void storeSimulation(Simulation simulation) {
		if (isAvailable()) {
			Connection storeSimConnection = null;
			//PreparedStatement updateSimStatement = null;
			PreparedStatement insertSimStatement = null;
			try {
				storeSimConnection = getConnection();
				Statement checkSimStatement = storeSimConnection.createStatement();
				checkSimStatement.executeQuery("SELECT identifier FROM "
						+ SIM_TABLE_NAME + " WHERE identifier = '"
						+ simulation.getIdentifier() + "'");
				ResultSet checkSimResultSet = checkSimStatement.getResultSet();
				if (checkSimResultSet.next()) {
					deleteSimulation(simulation.getIdentifier());
				}
					try {
						String insertQuery = ("insert into "
								+ SIM_TABLE_NAME
								+ "(name, description, collisionDistance, startTime,"
								+ " endTime, refData, username, referenceSystem, collisionCount, identifier, timeStamp, results)"
								+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
						insertSimStatement = storeSimConnection
								.prepareStatement(insertQuery);

						// Assigning values to DB fields
						insertSimStatement.setString(SIM_NAME_COL,
								simulation.getName());
						insertSimStatement.setString(SIM_DESC_COL,
								simulation.getDescription());
						insertSimStatement.setFloat(SIM_COLDIST_COL,
								simulation.getCollisionDistance());
						insertSimStatement.setTimestamp(SIM_STARTTIME_COL, new Timestamp(simulation.getStartTime().getTime()));
						insertSimStatement.setTimestamp(SIM_ENDTIME_COL, new Timestamp(simulation.getEndTime().getTime()));
						insertSimStatement.setString(SIM_REFDATA_COL,
								simulation.getRefData());
						insertSimStatement.setString(SIM_USERNAME_COL,
								simulation.getUsername());
						insertSimStatement.setString(SIM_REFSYS_COL,
								simulation.getReferenceSystem());
						insertSimStatement.setInt(SIM_COLCOUNT_COL,
								simulation.getCollisionCount());
						insertSimStatement.setString(SIM_ID_COL,
								simulation.getIdentifier());
						insertSimStatement.setTimestamp(SIM_TIMESTAMP_COL,
								new Timestamp(simulation.getTimeStamp()
										.getTime()));
						insertSimStatement.setString(SIM_RESULTS_COL,
								simulation.getResults());

						insertSimStatement.executeUpdate();
						storeSimConnection.close();
					} catch (SQLException e) {
						Debug.log(Debug.ERROR,
								"Failed to create a connection object");
						// System.out.println("error: failed to create a connection object.");
						e.printStackTrace();
					}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Delete specific simulation from the Simulations table
	 * 
	 * @param identifier
	 */
	public void deleteSimulation(String identifier) {
		if (isAvailable()) {
			Connection deleteSimConnection = null;

			try {
				deleteSimConnection = getConnection();
				try {
					String sqlStatment = "DELETE FROM " + SIM_TABLE_NAME
							+ " WHERE identifier = ?";
					PreparedStatement deleteSimStatement = deleteSimConnection
							.prepareStatement(sqlStatment);

					// Determine per identifier which simulation will be deleted
					deleteSimStatement.setString(1, identifier);

					deleteSimStatement.executeUpdate();
					deleteSimConnection.close();
				} catch (SQLException s) {
					Debug.log(Debug.ERROR,
							"DeleteSim SQL statement is not executed!");
					// System.out.println("SQL statement is not executed!");
					s.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Load Simulation from Simulations table and save it in ArrayList for showing on a main page
	 *
	 * @return
	 *
	 */
	public ArrayList<Simulation> loadSimulations() {
		Debug.log(Debug.TRACE, "Loading simulations...");
		Connection loadSimConnection = null;
		ArrayList<Simulation> simulations = new ArrayList<Simulation>();
		if (isAvailable()) {
			try {
				Debug.log(Debug.TRACE, "Establishing connection to DB...");
				loadSimConnection = getConnection();
				Debug.log(Debug.TRACE, "Connected!");
				Statement loadSimStatement = loadSimConnection.createStatement();
				Debug.log(Debug.TRACE, "Sending query...");
				loadSimStatement
						.executeQuery("SELECT name, description, collisionDistance, startTime, endTime,"
								+ " refData, username, referenceSystem, collisionCount, identifier, timeStamp, results FROM "
								+ SIM_TABLE_NAME);
				ResultSet loadSimResultSet = loadSimStatement.getResultSet();
				Debug.log(Debug.TRACE, "Query sent!");

				while (loadSimResultSet.next()) {
					String name = loadSimResultSet.getString(SIM_NAME_COL)
							.trim();
					String description = loadSimResultSet.getString(
							SIM_DESC_COL).trim();
					Float colDist = loadSimResultSet.getFloat(SIM_COLDIST_COL);
					Date startTime = new Date(loadSimResultSet.getTimestamp(SIM_STARTTIME_COL).getTime());
					Date endTime = new Date(loadSimResultSet.getTimestamp(SIM_ENDTIME_COL).getTime());
					String[] refData = loadSimResultSet.getString(
							SIM_REFDATA_COL).split("<br/>");
					String username = loadSimResultSet.getString(
							SIM_USERNAME_COL).trim();
					String refSystem = loadSimResultSet.getString(
							SIM_REFSYS_COL).trim();
					int collisionCount = loadSimResultSet
							.getInt(SIM_COLCOUNT_COL);
					String identifier = loadSimResultSet.getString(SIM_ID_COL)
							.trim();
					Date timeStamp = new Date(loadSimResultSet.getTimestamp(
							SIM_TIMESTAMP_COL).getTime());
					String results = loadSimResultSet
							.getString(SIM_RESULTS_COL).trim();

					Simulation simulation = new Simulation(name, description,
							colDist, startTime, endTime, refData, username,
							refSystem, collisionCount, identifier, timeStamp,
							results);
					simulations.add(simulation);
					Debug.log(Debug.TRACE,
							"Found simulation: " + simulation.getIdentifier());
				}
				Debug.log(Debug.TRACE, "Closing connection...");
				loadSimConnection.close();
				loadSimResultSet.close();
				Debug.log(Debug.TRACE, "Connection closed!");
				Debug.log(Debug.TRACE, simulations.size()
						+ " Simulations loaded!");
				return simulations;
			} catch (SQLException e) {
				Debug.log(Debug.DEBUG, "LoadSim query would not executed");
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return simulations;

	}

	/**
	 * Create table in DB for Users
	 * 
	 */
	private void createUserTable() {
		if (isAvailable()) {
			Connection createUserConnection = null;
			try {
				createUserConnection = getConnection();
				Statement createUserTableStatement = createUserConnection
						.createStatement();

				// Query for creating MySQL table SPUsers with columns: name,
				// password, isAdmin, language, referenceSystem
				String USER_TABLE = "CREATE TABLE IF NOT EXISTS "
						+ USER_TABLE_NAME
						+ " ("
						+ " name VARCHAR (12) PRIMARY KEY, password VARCHAR (18),"
						+ " isAdmin VARCHAR(6), language VARCHAR (30), referenceSystem VARCHAR (3))";
				createUserTableStatement.executeUpdate(USER_TABLE);
				createUserConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				Debug.log(Debug.ERROR, "Failed to open to " + USER_TABLE_NAME
						+ " Table");
				// e.printStackTrace();
			}
		}
	}

	/**
	 * Adds a new user, if it doesn't exist yet, or updates user data if user
	 * exists
	 * 
	 * @param user
	 */
	public void storeUser(User user) {
		if (isAvailable()) {
			Connection storeUserConnection = null;
			PreparedStatement insertUserPrepStatement = null;
			PreparedStatement updateUserPrepStatement = null;

			try {
				storeUserConnection = getConnection();
				Statement checkUserStatement = storeUserConnection.createStatement();
				checkUserStatement.executeQuery("SELECT name FROM "
						+ USER_TABLE_NAME + " WHERE name = '" + user.getName()
						+ "'");
				ResultSet checkUserResultSet = checkUserStatement
						.getResultSet();
				if (checkUserResultSet.next()) {
					try {
						String updateQuery = ("UPDATE " + USER_TABLE_NAME
								+ " SET password ='" + user.getPassword()
								+ "', isAdmin= '" + user.isAdmin().toString()
								+ "', language= '" + user.getLanguage()
								+ "', referenceSystem= '"
								+ user.getReferenceSystem()
								+ "' WHERE name = '" + user.getName() + "'");
						updateUserPrepStatement = storeUserConnection
								.prepareStatement(updateQuery);

						updateUserPrepStatement.executeUpdate();
					} catch (SQLException e) {
						Debug.log(Debug.ERROR,
								"SQL statement is not executed: store user!");
						e.printStackTrace();
					}
				} else {
					try {
						String insertQuery = ("insert into "
								+ USER_TABLE_NAME
								+ "(name, password, isAdmin, language, referenceSystem)" + " values (?,?, ?, ?, ?) ");
						insertUserPrepStatement = storeUserConnection
								.prepareStatement(insertQuery);

						// Assigning values to DB fields
						insertUserPrepStatement.setString(USER_NAME_COL,
								user.getName());
						insertUserPrepStatement.setString(USER_PASSWORD_COL,
								user.getPassword());
						insertUserPrepStatement.setString(USER_ISADMIN_COL,
								user.isAdmin().toString());
						insertUserPrepStatement.setString(USER_LANGUAGE_COL,
								user.getLanguage().toString());
						insertUserPrepStatement.setString(USER_REFSYS_COL,
								user.getReferenceSystem());

						insertUserPrepStatement.execute();
						// log.writeError("Benutzerdaten wurden geändert!");
					} catch (SQLException e) {
						Debug.log(Debug.ERROR,
								"Neuer Benutzer wurde nicht gespeichert!");
						e.printStackTrace();
					}
				}
				checkUserResultSet.close();
				storeUserConnection.close();
			} catch (SQLException e) {
				System.out
						.println("error: failed to create a connection object.");
				e.printStackTrace();
			} catch (Exception e) {
				System.out.println("other error:");
				e.printStackTrace();
			}
		}
	}

	/**
	 * Delete specific user from table SPUsers
	 * 
	 * @param user
	 */
	public void deleteUser(User user) {
		if (isAvailable()) {
			Connection deleteUserConnection = null;

			try {
				deleteUserConnection = getConnection();
				try {
					String sqlStatment = "DELETE FROM " + USER_TABLE_NAME
							+ " WHERE name = ?";
					PreparedStatement deleteUserStatement = deleteUserConnection
							.prepareStatement(sqlStatment);

					// Determine per name which user will be deleted
					deleteUserStatement.setString(1, user.getName());

					deleteUserStatement.executeUpdate();
					deleteUserStatement.close();
					deleteUserConnection.close();
				} catch (SQLException e) {
					Debug.log(Debug.ERROR,
							"Deleteuser SQL statement is not executed!");
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Load existing Users from SPUsers Table
	 * 
	 * @return
	 */
	public ArrayList<User> loadUsers() {
		Debug.log(Debug.TRACE, "Loading users...");

		Connection loadUserConnection = null;
		ArrayList<User> users = new ArrayList<User>();
		if (isAvailable()) {
			try {
				Debug.log(Debug.TRACE, "Establishing connection to DB...");
				loadUserConnection = getConnection();
				Debug.log(Debug.TRACE, "Connected!");
				Statement loadUserStatement = loadUserConnection.createStatement();
				Debug.log(Debug.TRACE, "Sending query...");
				loadUserStatement.executeQuery("SELECT * FROM "
						+ USER_TABLE_NAME);
				ResultSet loadUserResultSet = loadUserStatement.getResultSet();
				Debug.log(Debug.TRACE, "Query sent!");

				while (loadUserResultSet.next()) {
					String name = loadUserResultSet.getString(USER_NAME_COL)
							.trim();
					String password = loadUserResultSet.getString(
							USER_PASSWORD_COL).trim();
					Boolean isAdmin = Boolean.parseBoolean(loadUserResultSet
							.getString(USER_ISADMIN_COL).trim());
					Locale language = Locale.forLanguageTag(loadUserResultSet
							.getString(USER_LANGUAGE_COL).trim());
					String referenceSystem = loadUserResultSet.getString(
							USER_REFSYS_COL).trim();

					User user = new User(name, password, isAdmin, language,
							referenceSystem);
					users.add(user);
					Debug.log(Debug.TRACE, "Found user: " + user.getName());
				}
				Debug.log(Debug.TRACE, "Closing connection...");
				loadUserResultSet.close();
				loadUserConnection.close();
				Debug.log(Debug.TRACE, "Connection closed!");
				Debug.log(Debug.TRACE, users.size() + " Users loaded!");
				return users;
			} catch (SQLException e) {
				Debug.log(Debug.FATAL, "Failed to load users");
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

			// end DEBUG
			Debug.log(Debug.TRACE, "No Users Found");
		}
		return users;

	}

	/**
	 * Checks if the database server is available
	 * 
	 * @return TODO implement
	 */
	public boolean isAvailable() {
		return !url.equals("null");
	}

	/**
	 * Connect to Database
	 * 
	 * @return
	 */
	private Connection getConnection() {
		
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url + dbName, username, password);
		} catch (SQLException e) {
			Debug.log(Debug.FATAL, "Can't connect to SQL DB!");
		}
		return connection;
	}

	/*private boolean tableExists(String table) {
		try {
			Connection connection = getConnection();
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet res = meta.getTables(null, null, "%", new String[] { "TABLE" });
			while (res.next()) {
				Debug.log(Debug.TRACE, "Found Table: " + res.getString("TABLE_NAME"));
				if (res.getString("TABLE_NAME").equals(table)) {
					return true;
				}
			}
		} catch (Exception e) {
		}
		return false;

	}*/
}
